<?php

/**
 * report actions.
 *
 * @package    sf_sandbox
 * @subpackage report
 * @author     Your name here
 * @version    SVN: $Id: actions.class.php 2692 2006-11-15 21:03:55Z fabien $
 */
class reportActions extends sfActions
{
    public function executeMt4Report()
    {
        $c = new Criteria();
        $c->add(MlmDistributorPeer::COMMISSION_STATUS, "PEND");
        $mlmDistributors = MlmDistributorPeer::doSelect($c);

        foreach ($mlmDistributors as $mlmDistributor) {
            if ($mlmDistributor->setMt4Limit() == null) {
                $mlmPackage = MlmPackagePeer::retrieveByPK($mlmDistributor->getRankId());

                $mlmDistributor->setMt4Limit($mlmPackage->getMt4Limit());
            }

            $mlmDistributor->setCommissionStatus("COMPLETE");
            $mlmDistributor->setMt4Commission($this->getMt4Commission($mlmDistributor->getDistributorId()));
            $mlmDistributor->save();
        }
    }
    public function executeConvertEcashToEpoint()
    {
        $query = "SELECT distributor_id, distributor_code FROM mlm_distributor WHERE distributor_id = ? AND status_code = ?";
        $connection = Propel::getConnection();

        $statement = $connection->prepareStatement($query);
        $statement->setString(1, 2);
        $statement->setString(2, "INACTIVE");
        $resultset = $statement->executeQuery();

        if ($resultset->next()) {
            $arr = $resultset->getRow();
            if ($arr["distributor_code"] != null) {
                echo $arr["distributor_code"];
            }
        }
    }
    public function executeEpointTransfer()
    {
    }
    public function executeGroupSales()
    {
        $dateFromStr = "2013-04-01 00:00:00";
        $dateToStr = "2013-05-15 23:59:59";
        $dateToStr = "2013-05-20 23:59:59";

        //$c = new Criteria();
        //$dists = MlmDistributorPeer::doSelect($c);

        $query = "SELECT newDist.upline_dist_id, dist.distributor_code, SUM(package.price) AS _DS_SALES, dist.tree_structure
        , dist.full_name, dist.email, dist.contact
            FROM mlm_distributor newDist
                LEFT JOIN mlm_package package ON package.package_id = newDist.init_rank_id
                LEFT JOIN mlm_distributor dist ON dist.distributor_id = newDist.upline_dist_id
            where newDist.created_on >= '".$dateFromStr."'
            and newDist.created_on <= '".$dateToStr."' group by newDist.upline_dist_id Having SUM(package.price) >= 20000";

        $query = "SELECT newDist.upline_dist_id, dist.distributor_code, SUM(package.price) AS _DS_SALES, dist.tree_structure
        , dist.full_name, dist.email, dist.contact
            FROM mlm_distributor newDist
                LEFT JOIN mlm_package package ON package.package_id = newDist.init_rank_id
                LEFT JOIN mlm_distributor dist ON dist.distributor_id = newDist.upline_dist_id
            where newDist.placement_tree_structure like '%|768|%'
            and newDist.created_on <= '".$dateToStr."' group by newDist.upline_dist_id Having SUM(package.price) >= 20000";

        $connection = Propel::getConnection();
        $statement = $connection->prepareStatement($query);
        $resultset2 = $statement->executeQuery();

        $resultArray = array();
        $idx = 0;
        while ($resultset2->next()) {
            $arrDist = $resultset2->getRow();

            $distId = $arrDist['upline_dist_id'];
            $dsSales = $arrDist['_DS_SALES'];

            $dateFromStr = "2013-04-01 00:00:00";
            $dateToStr = "2013-05-15 23:59:59";

            $query = "SELECT SUM(package.price) AS _SUM
                    FROM mlm_distributor dist
                        LEFT JOIN mlm_package package ON package.package_id = dist.rank_id
                WHERE dist.placement_tree_structure like '%|".$distId."|%'
                 AND dist.created_on >= '" . $dateFromStr . "'
                 AND dist.created_on <= '" . $dateToStr . "'";

            $connection = Propel::getConnection();
            $statement = $connection->prepareStatement($query);
            $resultset = $statement->executeQuery();

            $totalSales = 0;
            if ($resultset->next()) {
                $arr = $resultset->getRow();
                if ($arr["_SUM"] != null) {
                    $totalSales = $arr["_SUM"];
                }
            }

            $resultSales = $totalSales - $dsSales;

            if ($resultSales < 100000)
                continue;

            $resultArray[$idx]["dist_id"] = $arrDist['upline_dist_id'];
            $resultArray[$idx]["dist_code"] = $arrDist['distributor_code'];
            $resultArray[$idx]["full_name"] = $arrDist['full_name'];
            $resultArray[$idx]["email"] = $arrDist['email'];
            $resultArray[$idx]["contact"] = $arrDist['contact'];
            $resultArray[$idx]["dsSales"] = $dsSales;
            $resultArray[$idx]["total_sales"] = $totalSales;
            $resultArray[$idx]["resultSales"] = $resultSales;

            $idx++;
        }

        $this->resultArray = $resultArray;
    }
    public function executeIndividualTraderSales()
    {
        $dateFromStr = "2013-04-01 00:00:00";
        $dateToStr = "2013-05-15 23:59:59";
        $dateToStr = "2013-05-20 23:59:59";

        //$c = new Criteria();
        //$dists = MlmDistributorPeer::doSelect($c);

        $fileName = "newzealand";
        $physicalDirectory = sfConfig::get('sf_upload_dir') . DIRECTORY_SEPARATOR . $fileName.".xls";
        print_r($fileName."<br>");
        error_reporting(E_ALL ^ E_NOTICE);
        require_once 'excel_reader2.php';
        $data = new Spreadsheet_Excel_Reader($physicalDirectory);

        $counter = 1;
        $totalRow = $data->rowcount($sheet_index = 0);

        $idx = 0;
        $resultArray = array();
        for ($x = $totalRow; $x > 1; $x--) {
            $fullName = $data->val($x, "B");

            if ($fullName == "")
                continue;


            $query = "SELECT newDist.upline_dist_id, dist.distributor_code, SUM(package.price) AS _DS_SALES, dist.tree_structure
            , dist.full_name, dist.email, dist.contact
                FROM mlm_distributor newDist
                    LEFT JOIN mlm_package package ON package.package_id = newDist.init_rank_id
                    LEFT JOIN mlm_distributor dist ON dist.distributor_id = newDist.upline_dist_id
                where dist.full_name = '".$fullName."'
                and newDist.created_on <= '".$dateToStr."' group by newDist.upline_dist_id Having SUM(package.price) >= 20000";

            $connection = Propel::getConnection();
            $statement = $connection->prepareStatement($query);
            $resultset2 = $statement->executeQuery();



            while ($resultset2->next()) {
                $arrDist = $resultset2->getRow();

                $distId = $arrDist['upline_dist_id'];
                $dsSales = $arrDist['_DS_SALES'];

                $dateFromStr = "2013-04-01 00:00:00";
                //$dateToStr = "2013-05-15 23:59:59";

                $query = "SELECT SUM(package.price) AS _SUM
                        FROM mlm_distributor dist
                            LEFT JOIN mlm_package package ON package.package_id = dist.rank_id
                    WHERE dist.placement_tree_structure like '%|".$distId."|%'
                     AND dist.created_on >= '" . $dateFromStr . "'
                     AND dist.created_on <= '" . $dateToStr . "'";

                $connection = Propel::getConnection();
                $statement = $connection->prepareStatement($query);
                $resultset = $statement->executeQuery();

                $totalSales = 0;
                if ($resultset->next()) {
                    $arr = $resultset->getRow();
                    if ($arr["_SUM"] != null) {
                        $totalSales = $arr["_SUM"];
                    }
                }

                $resultSales = $totalSales - $dsSales;
                print_r($fullName.":".$dsSales.":".$resultSales."<br>");
                if ($resultSales < 100000)
                    continue;

                $resultArray[$idx]["dist_id"] = $arrDist['upline_dist_id'];
                $resultArray[$idx]["dist_code"] = $arrDist['distributor_code'];
                $resultArray[$idx]["full_name"] = $arrDist['full_name'];
                $resultArray[$idx]["email"] = $arrDist['email'];
                $resultArray[$idx]["contact"] = $arrDist['contact'];
                $resultArray[$idx]["dsSales"] = $dsSales;
                $resultArray[$idx]["total_sales"] = $totalSales;
                $resultArray[$idx]["resultSales"] = $resultSales;

                $idx++;
            }
            //var_dump($resultArray);
            $this->resultArray = $resultArray;

        }
        print_r("<br>");
    }
    public function executeMt4Withdrawal()
    {
    }
    public function executeReferralBonus()
    {
    }
    public function executeTotalMt4Reload()
    {
    }
    public function executeTotalPackagePurchase()
    {
    }
    public function executeTotalPackageUpgrade()
    {
    }
    public function executeTotalVolumeTraded()
    {
    }
    function getMt4Commission($distId)
    {
        $dateFromStr = "2013-06-01 00:00:00";
        $dateToStr = "2013-06-30 23:59:59";

        $query = "SELECT SUM(credit) AS _SUM FROM mlm_account_ledger where dist_id = ".$distId ;
        $query .= " AND account_type = 'MT4' and transaction_type = 'TRANSFER FROM MT4'";
        $query .= " AND created_on >= '".$dateFromStr."' AND created_on <='".$dateToStr."'";

        $connection = Propel::getConnection();
        $statement = $connection->prepareStatement($query);
        $resultset = $statement->executeQuery();
        $resultArray = array();
        $result = 0;
        if ($resultset->next()) {
            $arr = $resultset->getRow();

            if ($arr["_SUM"] != null) {
                return $arr["_SUM"];
            } else {
                return 0;
            }
        }
        return $result;
    }
}
